﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Threading;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;
using Common.Utilities.OpenXml;
using System.IO;
using System.Reflection;

namespace Workflows.Components.ETL
{
    /// <summary>
    /// 
    /// </summary>
    public class DataSamplerWorker:IWorker 
    {
        #region Implementation of IWorker

        public event WorkerStarted WorkerStartedEvent;
        public event WorkerFinished WorkerFinishedEvent;
        public event WorkerHasError WorkerErredEvent;
        public event WorkerCancelled WorkerCancelledEvent;
        public event WorkerStatusChanged WorkerStatusChangedEvent;

        private Thread workerThread;
        private DataSamplerInstruction instruction;
        private DataSamplerOutput output;
        private RunContext _Context;
        private Guid _TicketID;
        private string _WorkflowName;
        private string _ComponentName;

        /// <summary>
        /// 
        /// </summary>
        public string ComponentName
        {
            get { return _ComponentName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public string WorkflowName
        {
            get { return _WorkflowName; }
        }

        /// <summary></summary>
        public Thread WorkerThread
        {
            get { return workerThread; }
        }
        /// <summary></summary>
        public InstructionBase Instruction
        {
            get { return instruction; }
        }
        /// <summary></summary>
        public ResultBase Output
        {
            get { return output; }
        }
        /// <summary></summary>
        public Type ComponentType
        {
            get { return typeof(DataSamplerComponent); }
        }
        /// <summary></summary>
        public RunContext Context
        {
            get { return _Context; }
            set { _Context = value; }
        }
        /// <summary></summary>
        public void SetInstruction(Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DataSamplerInstruction)request;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void SetInstruction(RunContext context, Guid ticketGuid, InstructionBase request, string workflowName, string componentName)
        {
            this._TicketID = ticketGuid;
            this.instruction = (DataSamplerInstruction)request;
            this._Context = context;
            this._WorkflowName = workflowName;
            this._ComponentName = componentName;
        }
        /// <summary></summary>
        public void DoWork()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = Thread.CurrentThread;
            this.GetSampleData();
        }
        /// <summary></summary>
        public void DoWorkAsync()
        {
            if (this.WorkerStartedEvent != null)
            {
                this.WorkerStartedEvent(this._TicketID, this, this.GetType(), this.instruction);
            }
            this.workerThread = new Thread(new ThreadStart(this.GetSampleData));
            this.workerThread.Start();
        }
        /// <summary></summary>
        public void CancelWork()
        {
            if (this.workerThread != null && this.workerThread.IsAlive)
            {
                this.workerThread.Abort();
                this.workerThread.Join();

                if (this.WorkerCancelledEvent != null)
                {
                    this.WorkerCancelledEvent(this._TicketID, this.GetType(), this.instruction);
                }
            }
        }

        #endregion

        #region work
        private void GetSampleData()
        {
            try
            {
                Dictionary<string, string> tableAlias = new Dictionary<string, string>();
                string sql = this.instruction.Sql;
                if (string.IsNullOrEmpty(sql))
                {
                    Dictionary<string, TableColumn> pks = new Dictionary<string, TableColumn>();
                    Dictionary<string, List<TableColumn>> fks = new Dictionary<string, List<TableColumn>>();
                    Dictionary<string, List<TableColumn>> indexedCols = new Dictionary<string, List<TableColumn>>();
                    string linkTableName = this.GetLinkTableName();
                    Dictionary<string, Dictionary<string, TableReference>> tblRefs =
                        this.ReadTableReferencesFromLinkTable(this.instruction.DataSourceName);

                    List<string> joinedTableNames = new List<string>();
                    foreach (string tblName in this.instruction.TableNames)
                    {
                        string[] parts = tblName.Split(new char[] {'.'});
                        string dbName = parts[0];
                        string connStr = string.Format(
                            "Data source={0};Database={1};Integrated Security=SSPI;",
                            this.instruction.DataSourceName, dbName);
                        string schemaName = parts[1];
                        string tblName2 = parts[2];
                        TableColumn[] tableColumns = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, schemaName, tblName2);
                        Dictionary<string, TableColumn> tableColumns2 = new Dictionary<string, TableColumn>();
                        foreach (TableColumn col in tableColumns)
                        {
                            tableColumns2.Add(col.ColumnName, col);
                        }
                        List<string> pkFieldNames = DbUtil.GetTablePrimaryKeys(connStr, schemaName, tblName2);
                        if (pkFieldNames != null && pkFieldNames.Count == 1)
                        {
                            pks.Add(tblName, tableColumns2[pkFieldNames[0]]);
                        }

                        string[] fkColNames = DbUtil.GetTableForeignKeyColumnNames(connStr, schemaName, tblName2);
                        if (fkColNames != null && fkColNames.Length > 0)
                        {
                            List<TableColumn> fkCols = new List<TableColumn>();
                            foreach (string fkColName in fkColNames)
                            {
                                fkCols.Add(tableColumns2[fkColName]);
                            }
                            fks.Add(tblName, fkCols);
                        }

                        string[] idxColNames = DbUtil.GetTableIndexedColumnNames(connStr, tblName2);
                        if(idxColNames !=null && idxColNames.Length>0)
                        {
                            List<TableColumn> idxCols=new List<TableColumn>();
                            foreach(string idxColName in idxColNames)
                            {
                                idxCols.Add(tableColumns2[idxColName]);
                            }
                            indexedCols.Add(tblName, idxCols);
                        }
                    }

                    string selectSql = string.Empty;
                    string fromSql = string.Empty;
                    int tblIdx = 0;
                    foreach (string tblName1 in this.instruction.TableNames)
                    {
                        tblIdx++;
                        if (fromSql.Length > 0)
                            fromSql += ", ";
                        string[] parts = tblName1.Split(new char[] { '.' });
                        string tblName2 = parts[2];
                        fromSql += string.Format("{0}.{1}.[{2}] T{3}", parts[0], parts[1], tblName2, tblIdx);
                        tableAlias.Add(tblName1, "T" + tblIdx);
                        foreach (string fieldName in this.instruction.TableFields[tblName1])
                        {
                            if (selectSql.Length > 0)
                                selectSql += ", ";
                            selectSql += string.Format("T{0}.[{1}]", tblIdx, fieldName);
                        }
                    }

                    string joinSql = string.Empty;
                    if (pks.Count > 0)
                    {
                        foreach (string parentTable in pks.Keys)
                        {
                            bool foundLink = false;
                            string tblAlias1 = tableAlias[parentTable];
                            string pkFieldName = pks[parentTable].ColumnName;
                            
                            foreach (string childTable in fks.Keys)
                            {
                                if (parentTable == childTable)
                                    continue;
                                string tblAlias2 = tableAlias[childTable];
                                string join = string.Empty;
                                foreach (TableColumn fkCol in fks[childTable])
                                {
                                    if (this.ColumnMatches(fkCol.ColumnName, pkFieldName))
                                    {

                                        join = string.Format(
                                            "{0}.[{1}]={2}.[{3}]",
                                            tblAlias1, pkFieldName,
                                            tblAlias2, fkCol.ColumnName);
                                        if (!joinedTableNames.Contains(parentTable))
                                            joinedTableNames.Add(parentTable);
                                        if (!joinedTableNames.Contains(childTable))
                                            joinedTableNames.Add(childTable);
                                        foundLink = true;
                                        break;
                                    }
                                }

                                if (join.Length > 0)
                                {
                                    if (joinSql.Length > 0)
                                        joinSql += " AND ";
                                    joinSql += join;
                                }
                            }

                            if (!foundLink)
                            {
                                foreach(string idxTblName in indexedCols.Keys)
                                {
                                    if(idxTblName==parentTable)
                                        continue;

                                    string tblAlias3 = tableAlias[idxTblName];
                                    string join = string.Empty;
                                    foreach (TableColumn idxCol in indexedCols[idxTblName])
                                    {
                                        if (this.ColumnMatches(idxCol.ColumnName, pkFieldName))
                                        {
                                            join = string.Format(
                                                "{0}.[{1}]={2}.[{3}]",
                                                tblAlias1, pkFieldName,
                                                tblAlias3, idxCol.ColumnName);
                                            if (!joinedTableNames.Contains(parentTable))
                                                joinedTableNames.Add(parentTable);
                                            if (!joinedTableNames.Contains(idxTblName))
                                                joinedTableNames.Add(idxTblName);
                                            foundLink = true;
                                            break;
                                        }
                                    }

                                    if (join.Length > 0)
                                    {
                                        if (joinSql.Length > 0)
                                            joinSql += " AND ";
                                        joinSql += join;
                                    }
                                }
                            }

                            if (!foundLink)
                            {
                                if (tblRefs != null && tblRefs.ContainsKey(parentTable.ToLower()))
                                {
                                    foreach (string childTable in this.instruction.TableNames)
                                    {
                                        if (childTable == parentTable)
                                            continue;

                                        if (tblRefs[parentTable.ToLower()].ContainsKey(childTable.ToLower()))
                                        {
                                            string join = this.CreateJoinSqlUsingLink(tableAlias, parentTable, childTable);
                                            if (!string.IsNullOrEmpty(join))
                                            {
                                                if (joinSql.Length > 0)
                                                    joinSql += " AND ";
                                                joinSql += join;
                                                foundLink = true;
                                                if (!joinedTableNames.Contains(parentTable))
                                                    joinedTableNames.Add(parentTable);
                                                if (!joinedTableNames.Contains(childTable))
                                                    joinedTableNames.Add(childTable);
                                                if (!joinedTableNames.Contains(linkTableName))
                                                    joinedTableNames.Add(linkTableName);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                    foreach(string tblName in this.instruction.TableNames)
                    {
                        if(!joinedTableNames.Contains(tblName))
                            throw new Exception("Unable to join table " + tblName);
                    }

                    sql = "SELECT " + selectSql + " FROM " + fromSql;
                    if (joinSql.Length > 0)
                        sql += " WHERE " + joinSql;

                    string filterSql = string.Empty;
                    if(this.instruction.FieldFilters !=null && this.instruction.FieldFilters.Count>0)
                    {
                        foreach(string fieldName in this.instruction.FieldFilters.Keys)
                        {
                            string tblName1 = string.Empty;
                            foreach(string tblName in this.instruction.TableFields.Keys)
                            {
                                if(this.instruction.TableFields[tblName].Contains(fieldName))
                                {
                                    tblName1 = tblName;
                                    break;
                                }
                            }

                            string tblAlias = tableAlias[tblName1];
                            string fieldFilter = this.instruction.FieldFilters[fieldName];
                            if (fieldFilter.Contains(fieldName))
                                fieldFilter = fieldFilter.Replace(fieldName,
                                                                  string.Format("{0}.[{1}]", tblAlias, fieldName));
                            else
                                fieldFilter = string.Format("{0}.{1} {2}", tblAlias, fieldName, fieldFilter);
                            if (filterSql.Length > 0)
                                filterSql += " AND ";
                            filterSql += fieldFilter;
                        }
                    }

                    if(filterSql.Length>0)
                    {
                        if(joinSql.Length>0)
                        {
                            sql += " AND " + filterSql;
                        }
                        else
                            sql += " WHERE " + filterSql;
                    }
                }

                string connStr2 = string.Format(
                        "Data source={0};Database={1};Integrated Security=SSPI;",
                        this.instruction.DataSourceName, "Master");

                int recCount = this.instruction.SampleSize;
                string idSampleTableName = string.Empty;
                if (string.IsNullOrEmpty(this.instruction.SampleByField))
                {
                    int totalRecCount = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, connStr2, sql);
                    if (this.instruction.SamplingType == SamplingMethod.Count && this.instruction.SampleSize < recCount)
                        recCount = this.instruction.SampleSize;
                    else
                        recCount = this.instruction.SampleSize * totalRecCount / 100;
                }
                else
                {
                    string tblName3 = string.Empty;
                    foreach(string tblName in this.instruction.TableFields.Keys)
                    {
                        if(this.instruction.TableFields[tblName].Contains(this.instruction.SampleByField))
                        {
                            tblName3 = tblName;
                            break;
                        }
                    }
                    int count = this.instruction.SampleSize;
                    int pct = 0;
                    if(this.instruction.SamplingType==SamplingMethod.Percent)
                    {
                        pct = this.instruction.SampleSize;
                        count = 0;
                    }
                    idSampleTableName =
                        this.PopulateSampleIDIntoTempTable(
                            this.instruction.DataSourceName, tblName3,
                            this.instruction.SampleByField, count, pct,
                            this.instruction.FieldFilters);
                    string[] parts = tblName3.Split(new char[] {'.'});
                    string tblAlias = parts[2];
                    if (tableAlias != null && tableAlias.ContainsKey(tblName3))
                        tblAlias = tableAlias[tblName3];
                    if (sql.ToLower().IndexOf("where") > 0)
                        sql += string.Format(" AND {0}.[{1}] in (select [{1}] from ##{2})",
                                             tblAlias, this.instruction.SampleByField, idSampleTableName);
                    else
                        sql += string.Format(" WHERE {0}.[{1}] in (select [{1}] from ##{2})",
                                             tblAlias, this.instruction.SampleByField, idSampleTableName);
                }
                TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr2, sql);
                Dictionary<string,TableColumn> cols2=new Dictionary<string, TableColumn>();
                foreach (TableColumn col in cols)
                    if (!cols2.ContainsKey(col.ColumnName))
                        cols2.Add(col.ColumnName, col);
                Dictionary<string,int> filterSqlCount=new Dictionary<string, int>();
                foreach(string fieldName in this.instruction.FieldDistributions.Keys)
                {
                    if(this.instruction.FieldDistributions[fieldName]!=null && 
                        this.instruction.FieldDistributions[fieldName].Count>0)
                    {
                        Dictionary<object, int> valPcts = this.instruction.FieldDistributions[fieldName];
                        if(filterSqlCount.Count==0)
                        {
                            foreach(object val in valPcts.Keys)
                            {
                                int countByFieldValue = valPcts[val]*recCount/100;
                                string fieldValue = FieldDataType.ToMssqlDataValue(cols2[fieldName].DbType, val.ToString());
                                string filterSql = string.Format("{0}={1}", fieldName, fieldValue);
                                filterSqlCount.Add(filterSql, countByFieldValue);
                            }
                        }
                        else
                        {
                            Dictionary<string,int> filterSqlCount2=new Dictionary<string, int>();
                            foreach(object val in valPcts.Keys)
                            {
                                foreach(string filterSql1 in filterSqlCount.Keys)
                                {
                                    int countByFieldValues = valPcts[val]*filterSqlCount[filterSql1];
                                    string fieldValue = FieldDataType.ToMssqlDataValue(cols2[fieldName].DbType,
                                                                                       val.ToString());
                                    string filterSql2 = filterSqlCount[filterSql1] + " AND " + string.Format("{0}={1}", fieldName, fieldValue);
                                    filterSqlCount2.Add(filterSql2, countByFieldValues);
                                }
                            }
                            filterSqlCount = filterSqlCount2;
                        }
                    }
                }

                DataTable dt = null;
                if (filterSqlCount.Count > 0)
                {
                    if (sql.ToLower().IndexOf("where") < 0)
                        sql += " WHERE ";
                    else
                        sql += " AND ";
                    string sql2 = string.Empty;
                    int sectionIdx = 0;
                    foreach (string filterSql in filterSqlCount.Keys)
                    {
                        sql2 = sql + filterSql;
                        int count = filterSqlCount[filterSql];
                        sectionIdx++;
                        DataTable dtSection = DbUtil.GetTableDataSampleBySQL(
                            connStr2, "Samples_" + sectionIdx, sql2, 0, count);
                        if (dt == null)
                            dt = dtSection;
                        else
                        {
                            foreach (DataRow drSec in dtSection.Rows)
                            {
                                DataRow dr = dt.NewRow();
                                for (int i = 0; i < dtSection.Columns.Count; i++)
                                {
                                    if (dt.Columns.Contains(dtSection.Columns[i].ColumnName))
                                        dr[dtSection.Columns[i].ColumnName] = drSec[i];
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                    }
                }
                else
                {
                    int pct = 0;
                    int count = 0;
                    if (this.instruction.SamplingType == SamplingMethod.Percent)
                        pct = Math.Max(0, Math.Min(100, this.instruction.SampleSize));
                    else
                        count = this.instruction.SampleSize;
                    dt = DbUtil.GetTableDataSampleBySQL(connStr2, "Samples", sql, pct, count);
                }

                if (!string.IsNullOrEmpty(this.instruction.AggregateField))
                {
                    dt = this.AggregateData(dt);
                }

                if(this.instruction.OutputFilePath.ToLower().EndsWith(".xlsx"))
                {
                    DataSet ds=new DataSet();
                    ds.Tables.Add(dt.Copy());
                    ExcelWriter.CreateWorkbook(this.instruction.OutputFilePath, ds);
                }
                else
                {
                    XmlDocument xDoc = new XmlDocument();
                    xDoc.LoadXml("<Samples></Samples>");
                    XmlNode root = xDoc.DocumentElement;
                    XmlNode colNodes = XmlDataUtil.AddElement(ref xDoc, root, "Columns");
                    foreach(DataColumn col in dt.Columns)
                    {
                        XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, colNodes, "Column");
                        XmlDataUtil.UpdateAttribute(ref xDoc, colNode,"ColumnName", col.ColumnName);
                    }
                    XmlNode rowNodes = XmlDataUtil.AddElement(ref xDoc, root, "Rows");
                    foreach(DataRow dr in dt.Rows)
                    {
                        XmlNode rowNode = XmlDataUtil.AddElement(ref xDoc, rowNodes, "Row");
                        for(int i=0;i<dt.Columns.Count;i++)
                        {
                            if(dr[i]!=null && dr[i]!=DBNull.Value)
                            {
                                XmlNode cellNode = XmlDataUtil.AddElement(ref xDoc, rowNode, "Cell");
                                XmlDataUtil.UpdateAttribute(ref xDoc, cellNode, "FieldName", dt.Columns[i].ColumnName);
                                XmlDataUtil.UpdateAttribute(ref xDoc, cellNode,"FieldValue", dr[i].ToString());
                            }
                        }
                    }
                    xDoc.Save(this.instruction.OutputFilePath);
                }

                if (!string.IsNullOrEmpty(idSampleTableName))
                {
                    string connStr = string.Format("Data source={0};Database=Master;Integrated security=sspi;", this.instruction.DataSourceName);
                    string dropTableSql = string.Format("drop table ##{0}", idSampleTableName);
                    DbUtil.ExecuteSqlCommand(connStr, dropTableSql);
                }


                this.output=new DataSamplerOutput(this.instruction.OwnerComponentID,this._TicketID,this.instruction.OutputFilePath);
                if (this.WorkerFinishedEvent != null)
                    this.WorkerFinishedEvent(this._TicketID, this.GetType(), this.instruction, this.output);
            }
            catch(Exception ex)
            {
                if (this.WorkerErredEvent != null)
                    this.WorkerErredEvent(this._TicketID, this.GetType(), this.instruction, ex.ToString(), true);
            }
            
        }
        #endregion

        #region column matches
        private bool ColumnMatches(string colName1, string colName2)
        {
            colName1 = StringUtil.RemoveNonAlphaNumericCharacters(colName1);
            colName2 = StringUtil.RemoveNonAlphaNumericCharacters(colName2);
            if (colName1.ToLower() == colName2.ToLower())
                return true;
            else
                return false;
        }
        #endregion

        #region link table 
        private bool ContainsLinkTable(string dbName, string schemaName, string tblName)
        {
            string appFolderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sqlFilePath = appFolderPath + "\\SQL\\Link.xml";
            if(File.Exists(sqlFilePath))
            {
                XmlDocument xDoc=new XmlDocument();
                xDoc.Load(sqlFilePath);
                XmlNode root=xDoc.DocumentElement;
                string dbName1=XmlDataUtil.GetAttributeValue(root,"DbName","SynapseNew");
                string schema1=XmlDataUtil.GetAttributeValue(root,"SchemaName","dbo");
                string tblName1=XmlDataUtil.GetAttributeValue(root,"TableName","Link");
                if(dbName.ToLower()==dbName1.ToLower() && 
                    schemaName.ToLower()==schema1.ToLower() && 
                    tblName.ToLower()==tblName1.ToLower())
                {
                    return true;
                }
            }
            return false;
        }

        private string GetLinkTableName()
        {
            string appFolderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sqlFilePath = appFolderPath + "\\SQL\\Link.xml";
            if (File.Exists(sqlFilePath))
            {
                XmlDocument xDoc = new XmlDocument();
                xDoc.Load(sqlFilePath);
                XmlNode root = xDoc.DocumentElement;
                string dbName1 = XmlDataUtil.GetAttributeValue(root, "DbName", "SynapseNew");
                string schema1 = XmlDataUtil.GetAttributeValue(root, "SchemaName", "dbo");
                string tblName1 = XmlDataUtil.GetAttributeValue(root, "TableName", "Link");
                return dbName1 + "." + schema1 + "." + tblName1;
            }
            else
                return string.Empty;
        }

        private Dictionary<string, Dictionary<string, TableReference>> ReadTableReferencesFromLinkTable(string dataSrc)
        {
            Dictionary<string, Dictionary<string, TableReference>> tblRefs = 
                new Dictionary<string,Dictionary<string,TableReference>>();
            string appFolderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sqlFilePath = appFolderPath + "\\SQL\\Link.xml";
            if(File.Exists(sqlFilePath))
            {
                XmlDocument xDoc=new XmlDocument();
                xDoc.Load(sqlFilePath);
                XmlNode root=xDoc.DocumentElement;
                string dbName1=XmlDataUtil.GetAttributeValue(root,"DbName","SynapseNew");
                string schema1=XmlDataUtil.GetAttributeValue(root,"SchemaName","dbo");
                string tblName1=XmlDataUtil.GetAttributeValue(root,"TableName","Link");
                string fromTypeField= XmlDataUtil.GetAttributeValue(root,"FromTypeField","FromObjectTypeID");
                string toTypeField=XmlDataUtil.GetAttributeValue(root,"ToTypeField","ToObjectTypeID");
                string fromIDField=XmlDataUtil.GetAttributeValue(root,"FromIDField","FromObjectID");
                string toIDField=XmlDataUtil.GetAttributeValue(root, "ToIDField","ToObjectID");
                string connStr=string.Format("Data Source={0};Database={1};Integrated Security=SSPI;",dataSrc,dbName1);
                string sql=@"
select {0}, {1}, count (*) as LinkageCount 
from {2}.[{3}] 
group by {0}, {1}
order by {0}, {1}";
                sql=string.Format(sql, fromTypeField, toTypeField, schema1, tblName1);
                DataTable dt=DbUtil.GetTableDataBySQL(connStr,"LinkCount",sql);
                Dictionary<int,List<int>> typeLinks=new Dictionary<int,List<int>>();
                foreach(DataRow dr in dt.Rows)
                {
                    int fromTypeID=int.Parse(dr[fromTypeField].ToString());
                    int toTypeID=int.Parse(dr[toTypeField].ToString());
                    if (!typeLinks.ContainsKey(fromTypeID))
                    {
                        List<int> toTypeIDs=new List<int>();
                        toTypeIDs.Add(toTypeID);
                        typeLinks.Add(fromTypeID, toTypeIDs);
                    }
                    else 
                    {
                        List<int> toTypeIDs=typeLinks[fromTypeID];
                        if(!toTypeIDs.Contains(toTypeID))
                            toTypeIDs.Add(toTypeID);
                        typeLinks[fromTypeID]=toTypeIDs;
                    }
                    //if(!typeLinks.ContainsKey(toTypeID))
                    //{
                    //    List<int> toTypeIDs=new List<int>();
                    //    toTypeIDs.Add(fromTypeID);
                    //    typeLinks.Add(toTypeID, toTypeIDs);
                    //}
                    //else 
                    //{
                    //    List<int> toTypeIDs=typeLinks[toTypeID];
                    //    if(!toTypeIDs.Contains(fromTypeID))
                    //        toTypeIDs.Add(fromTypeID);
                    //    typeLinks[toTypeID]=toTypeIDs;
                    //}
                }

                XmlNodeList linkNodes=root.SelectNodes("LinkType");
                Dictionary<int,string[]> linkEntities=new Dictionary<int,string[]>();
                foreach(XmlNode linkNode in linkNodes)
                {
                    int typeID=int.Parse(XmlDataUtil.GetAttributeValue(linkNode,"TypeID","0"));
                    string dbName=XmlDataUtil.GetAttributeValue(linkNode,"DbName","");
                    string schemaName=XmlDataUtil.GetAttributeValue(linkNode,"SchemaName","");
                    string tblName=XmlDataUtil.GetAttributeValue(linkNode,"TableName","");
                    string pk=XmlDataUtil.GetAttributeValue(linkNode,"PK","");
                    linkEntities.Add(typeID, new string[] {dbName, schemaName, tblName, pk});
                }

                foreach(int fromTypeID in typeLinks.Keys)
                {
                    if(!linkEntities.ContainsKey(fromTypeID))
                        continue;
                    string[] fromParts=linkEntities[fromTypeID];
                    string fromTableName=fromParts[0]+"."+fromParts[1]+"."+fromParts[2];
                    foreach(int toTypeID in typeLinks[fromTypeID])
                    {
                        if(!linkEntities.ContainsKey(toTypeID) || fromTypeID==toTypeID)
                            continue;

                        string[] toParts=linkEntities[toTypeID];
                        string toTableName=toParts[0]+"."+toParts[1]+"."+toParts[2];
                        TableReference tblRef = new TableReference(
                            fromParts[0], fromParts[1], fromParts[2], fromParts[3],
                            toParts[0], toParts[1], toParts[2], toParts[3]);
                        Dictionary<string,TableReference> toTblRefs=new Dictionary<string,TableReference>();
                        if (tblRefs.ContainsKey(fromTableName.ToLower()))
                            toTblRefs = tblRefs[fromTableName.ToLower()];
                        if (!toTblRefs.ContainsKey(toTableName.ToLower()))
                            toTblRefs.Add(toTableName.ToLower(), tblRef);
                        if (tblRefs.ContainsKey(fromTableName.ToLower()))
                            tblRefs[fromTableName.ToLower()] = toTblRefs;
                        else 
                            tblRefs.Add(fromTableName.ToLower(), toTblRefs);
                    }
                }
            }
            return tblRefs;
        }

        private string CreateJoinSqlUsingLink(
            Dictionary<string,string> tableAlias, 
            string tblName1, string tblName2)
        {
            string[] parts1=tblName1.Split(new char[]{'.'});
            string[] parts2=tblName2.Split(new char[]{'.'});
            string appFolderPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string sqlFilePath = appFolderPath + "\\SQL\\Link.xml";
            if(File.Exists(sqlFilePath))
            {
                XmlDocument xDoc=new XmlDocument();
                xDoc.Load(sqlFilePath);
                XmlNode root=xDoc.DocumentElement;
                string fromTypeField= XmlDataUtil.GetAttributeValue(root,"FromTypeField","FromObjectTypeID");
                string toTypeField=XmlDataUtil.GetAttributeValue(root,"ToTypeField","ToObjectTypeID");
                string fromIDField=XmlDataUtil.GetAttributeValue(root,"FromIDField","FromObjectID");
                string toIDField=XmlDataUtil.GetAttributeValue(root, "ToIDField","ToObjectID");
                string linkDbName=XmlDataUtil.GetAttributeValue(root,"DbName","SynapseNew");
                string linkSchema=XmlDataUtil.GetAttributeValue(root,"SchemaName","dbo");
                string linkTblName=XmlDataUtil.GetAttributeValue(root,"TableName","Link");
                string linkTableName=linkDbName +"."+linkSchema +"."+linkTblName;
                int fromTypeID=0;
                string fromKey=string.Empty;
                int toTypeID=0;
                string toKey=string.Empty;
                    
                XmlNodeList linkNodes=root.SelectNodes("LinkType");
                foreach(XmlNode linkNode in linkNodes)
                {
                    int typeID = int.Parse(XmlDataUtil.GetAttributeValue(linkNode, "TypeID", "0"));
                    string dbName=XmlDataUtil.GetAttributeValue(linkNode,"DbName","");
                    string schemaName=XmlDataUtil.GetAttributeValue(linkNode,"SchemaName","");
                    string tblName=XmlDataUtil.GetAttributeValue(linkNode,"TableName","");
                    string pk=XmlDataUtil.GetAttributeValue(linkNode,"PK","");
                    if(dbName.ToLower()==parts1[0].ToLower() && 
                        schemaName.ToLower()==parts1[1].ToLower() && 
                        tblName.ToLower()==parts1[2].ToLower())
                    {
                        fromTypeID=typeID;
                        fromKey=pk;
                    }
                    else if(dbName.ToLower()==parts2[0].ToLower() && 
                        schemaName.ToLower()==parts2[1].ToLower() && 
                        tblName.ToLower()==parts2[2].ToLower())
                    {
                        toTypeID=typeID;
                        toKey=pk;
                    }
                }

                if(fromTypeID>0 && toTypeID>0)
                {
                    string joinSql=string.Format(
                        "{0}.{3}={2}.{5} AND {1}.{4}={2}.{6} AND {2}.{7}={9} and {2}.{8}={10}",
                        tableAlias[tblName1],
                        tableAlias[tblName2],
                        tableAlias[linkTableName],
                        fromKey, toKey, fromIDField, toIDField,
                        fromTypeField, toTypeField,
                        fromTypeID, toTypeID);
                    return joinSql;
                }
            }
            return string.Empty;
        }
        #endregion

        #region sample field
        private string PopulateSampleIDIntoTempTable(
            string dataSrc, string tblName, 
            string sampleByField, int count, int pct,
            Dictionary<string,string> fieldFilters)
        {
            string[] parts = tblName.Split(new char[] {'.'});
            string connStr = string.Format("Data source={0};database={1};integrated security=sspi", dataSrc, parts[0]);
            TableColumn[] cols = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, parts[1], parts[2]);
            Dictionary<string,SqlDbType> colDbTypes=new Dictionary<string, SqlDbType>();
            foreach(TableColumn col in cols)
                colDbTypes.Add(col.ColumnName, col.DbType);
            string filterSql = string.Empty;
            string tblAlias = "T1";
            foreach(string fieldName in colDbTypes.Keys)
            {
                if(fieldFilters !=null && fieldFilters.ContainsKey(fieldName))
                {
                    string fieldFilter = fieldFilters[fieldName];
                    if (fieldFilter.Contains(fieldName))
                        fieldFilter = fieldFilter.Replace(fieldName,
                                                          string.Format("{0}.[{1}]", tblAlias, fieldName));
                    else
                        fieldFilter = string.Format("{0}.{1} {2}", tblAlias, fieldName, fieldFilter);
                    if (filterSql.Length > 0)
                        filterSql += " AND ";
                    filterSql += fieldFilter;
                }
            }
            string sql = string.Format("select count (distinct [{0}]) from {1}.[{2}] {3} where [{0}] is not null", sampleByField, parts[1], parts[2], tblAlias);
            if(filterSql.Length>0)
                sql += " AND " + filterSql;
            int totalCount = DbUtil.GetTableRecordCount(DataProviderType.MSSQL, connStr, sql);
            int recCount = count;
            if (count > 0 && count < totalCount)
            {
                recCount = count;
            }
            else if(count>0)
            {
                recCount = totalCount;
            }
            else
            {
                recCount = totalCount*pct/100;
            }
            string sampleTableName = sampleByField + "_Sample";
            sql = string.Format(
                "select top {0} {5}.[{1}] into ##{4} from {2}.[{3}] {5} "+
                "where {5}.[{1}] is not null", 
                recCount, sampleByField, parts[1], parts[2], 
                sampleTableName, tblAlias);
            if (filterSql.Length > 0)
                sql += " AND " + filterSql;
            DbUtil.ExecuteSqlCommand(connStr, sql);
            return sampleTableName;
        }
        #endregion

        #region aggregate
        private DataTable AggregateData(DataTable dt)
        {
            DataTable dt2 = new DataTable();
            SqlDbType idDbType = SqlDbType.Int;
            Dictionary<string,SimpleDataType> fieldSimpleDbTypes=new Dictionary<string, SimpleDataType>();
            foreach(string tblName in this.instruction.TableFields.Keys)
            {
                Dictionary<string,TableColumn> cols=new Dictionary<string, TableColumn>();
                string[] parts = tblName.Split(new char[] {'.'});
                string connStr = string.Format("Data source={0};Database={1};Integrated security=sspi;",
                                               this.instruction.DataSourceName, parts[0]);
                TableColumn[] cols1 = DbUtil.GetTableColumns(DataProviderType.MSSQL, connStr, parts[1], parts[2]);
                foreach(TableColumn col in cols1)
                    cols.Add(col.ColumnName, col);

                foreach(string fieldName in this.instruction.TableFields[tblName])
                {
                    if(fieldName.ToLower()!=this.instruction.AggregateField.ToLower() && 
                        this.instruction.FieldAggregateFunctions !=null && 
                        this.instruction.FieldAggregateFunctions[fieldName]==AggregateFunction.Skip)
                        continue;

                    if(fieldName.ToLower()==this.instruction.AggregateField.ToLower())
                        idDbType = cols[fieldName].DbType;
                    Type fieldType = FieldDataType.ToSystemType(cols[fieldName].DbType);
                    if (this.instruction.FieldAggregateFunctions != null && this.instruction.FieldAggregateFunctions.ContainsKey(fieldName))
                        fieldType =
                            this.GetAggregateFunctionReturnType(this.instruction.FieldAggregateFunctions[fieldName],
                                                                cols[fieldName].DbType);
                    if (this.instruction.FieldsAlias !=null && this.instruction.FieldsAlias.ContainsKey(fieldName))
                        dt2.Columns.Add(this.instruction.FieldsAlias[fieldName], fieldType);
                    else
                        dt2.Columns.Add(fieldName, fieldType);

                    fieldSimpleDbTypes.Add(fieldName, SimpleDataTypeUtil.ToSimpleDataType(cols[fieldName].DbType));
                }
            }
            List<string> distIDs = new List<string>();
            foreach(DataRow dr1 in dt.Rows)
            {
                string id = dr1[this.instruction.AggregateField].ToString().ToLower();
                if(!distIDs.Contains(id))
                    distIDs.Add(id.ToLower());
            }
            foreach(string id in distIDs)
            {
                DataRow[] drs =
                    dt.Select(string.Format("{0}={1}", this.instruction.AggregateField,
                                            FieldDataType.ToMssqlDataValue(idDbType, id)));
                Dictionary<string,object> aggregateValues=new Dictionary<string, object>();
                aggregateValues.Add(this.instruction.AggregateField,
                                    SimpleDataTypeUtil.ToDbValue(fieldSimpleDbTypes[this.instruction.SampleByField], id));
                foreach(string fieldName in this.instruction.FieldAggregateFunctions.Keys)
                {
                    if(fieldName==this.instruction.AggregateField || this.instruction.FieldAggregateFunctions[fieldName]==AggregateFunction.Skip)
                        continue;

                    List<object> valueCollection=new List<object>();
                    foreach(DataRow dr1 in drs)
                    {
                        if(dr1[fieldName] !=null && dr1[fieldName] != DBNull.Value)
                            valueCollection.Add(dr1[fieldName]);
                    }
                    object aggrValue =
                        this.GetAggregateValue(this.instruction.FieldAggregateFunctions[fieldName],
                                               valueCollection);
                    aggregateValues.Add(fieldName, aggrValue);
                }

                DataRow dr2 = dt2.NewRow();
                foreach(string fieldName in aggregateValues.Keys)
                {
                    string colName = fieldName;
                    if (this.instruction.FieldsAlias != null && this.instruction.FieldsAlias.ContainsKey(fieldName))
                        colName = this.instruction.FieldsAlias[fieldName];
                    if (dt2.Columns.Contains(colName))
                        dr2[colName] = aggregateValues[fieldName];
                }
                dt2.Rows.Add(dr2);
            }

            return dt2;
        }

        private object GetAggregateValue(
            AggregateFunction aggrFunction, 
            List<object> valueCollection)
        {
            if(valueCollection==null && valueCollection.Count==0)
                return DBNull.Value;

            switch (aggrFunction)
            {
                case AggregateFunction.Count:
                    return valueCollection.Count;
                case AggregateFunction.CountDistinct:
                    List<string> distValues = new List<string>();
                    foreach(object val in valueCollection)
                        if(!distValues.Contains(val.ToString().ToLower()))
                            distValues.Add(val.ToString().ToLower());
                    return distValues.Count;
                case AggregateFunction.Avg:
                    List<double> nums=new List<double>();
                    foreach(object val in valueCollection)
                    {
                        double num = 0;
                        bool tryParse = double.TryParse(val.ToString(), out num);
                        if(tryParse)
                            nums.Add(num);
                    }
                    return StatsUtil.GetAverage(nums).Value;
                case AggregateFunction.Max:
                    valueCollection.Sort();
                    return valueCollection[valueCollection.Count - 1];
                case AggregateFunction.Median:
                    valueCollection.Sort();
                    return valueCollection[valueCollection.Count/2];
                case AggregateFunction.Min:
                    valueCollection.Sort();
                    return valueCollection[0];
                case AggregateFunction.Mode:
                    Dictionary<string,int> valCount=new Dictionary<string, int>();
                    foreach (object val in valueCollection)
                    {
                        string valstr = val.ToString().ToLower();
                        if (valCount.ContainsKey(valstr))
                            valCount[valstr] = valCount[valstr] + 1;
                        else 
                            valCount.Add(valstr, 1);
                    }
                    string modeStr = string.Empty;
                    int maxCount = 0;
                    foreach(string valStr in valCount.Keys)
                    {
                        if(valCount[valStr]>maxCount)
                        {
                            maxCount = valCount[valStr];
                            modeStr = valStr;
                        }
                    }
                    foreach(object val in valueCollection)
                    {
                        if(val.ToString().ToLower()==modeStr)
                            return val;
                    }
                    return null;
                case AggregateFunction.SD:
                    List<double> nums2 = new List<double>();
                    foreach (object val in valueCollection)
                    {
                        double num = 0;
                        bool tryParse = double.TryParse(val.ToString(), out num);
                        if (tryParse)
                            nums2.Add(num);
                    }
                    return StatsUtil.GetStandardDeviation(nums2).Value;
                case AggregateFunction.Sum:
                    double sum = 0;
                    foreach (object val in valueCollection)
                    {
                        double num = 0;
                        bool tryParse = double.TryParse(val.ToString(), out num);
                        if (tryParse)
                            sum += num;
                    }
                    return sum;
                case AggregateFunction.Skip:
                    return null;
                case AggregateFunction.Top:
                    if (valueCollection.Count > 0)
                        return valueCollection[0];
                    else
                        return null;
                default:
                    return null;
            }
        }

        private Type GetAggregateFunctionReturnType(AggregateFunction aggrFunc, SqlDbType dbType)
        {
            if (aggrFunc == AggregateFunction.CountDistinct || aggrFunc == AggregateFunction.Count)
                return typeof(int);
            else
                return FieldDataType.ToSystemType(dbType);
        }


        #endregion
    }
}
